﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data;

/*
Todo: Get view selection combo working correctly
		- re-order: do this on save with the view def manager
      descending sort implemented
      Extra filter.
      Filter clause
      search field types eg. numeric search not working
*/

namespace DbView
{
	/*
		A schema field defines one of the fields available to the view
		It may be reported, sorted on or used in a filter search of custom search.
	*/
	public class SchemaField
	{
		public const String SCHEMA_TYPE_STRING = "S";
		public const String SCHEMA_TYPE_INTEGER = "I";
		public const String SCHEMA_TYPE_BBIGINTEGER = "J";
		public const String SCHEMA_TYPE_NUMBER = "N";
		public const String SCHEMA_TYPE_DATE = "D";
		public const String SCHEMA_TYPE_BOOL = "B";

		public SchemaField(String name, String type)
		{
			this.name = name;
			this.type = type;
		}

		public String Name { get { return this.name; } }
		public String Type { get { return this.type; } }
		public String Ord { get { return (this.order == "D") ? this.order : "A"; } set { this.order = value; } }
		// Safe name for SQL statements to cope with wierd fields in SQl 
		public String SqlSafeName { get { return String.Format("[{0}]", this.name); } }
		public String DoubleQuotedName { get { return String.Format("\"{0}\"", this.name); } }

		public static String TranslateSystemType(String systemTypeName)
		{
			return (systemTypeName == typeof(System.Int32).ToString()) ? SCHEMA_TYPE_INTEGER :
					(systemTypeName == typeof(System.Int16).ToString()) ? SCHEMA_TYPE_INTEGER :
					(systemTypeName == typeof(System.Int64).ToString()) ? SCHEMA_TYPE_BBIGINTEGER :
					(systemTypeName == typeof(System.Double).ToString()) ? SCHEMA_TYPE_NUMBER :
					(systemTypeName == typeof(System.Decimal).ToString()) ? SCHEMA_TYPE_NUMBER :
					(systemTypeName == typeof(System.Single).ToString()) ? SCHEMA_TYPE_NUMBER :
					(systemTypeName == typeof(System.DateTime).ToString()) ? SCHEMA_TYPE_DATE :
					(systemTypeName == typeof(System.DateTimeOffset).ToString()) ? SCHEMA_TYPE_DATE :
					(systemTypeName == typeof(System.Boolean).ToString()) ? SCHEMA_TYPE_BOOL :
					(systemTypeName == typeof(System.Byte).ToString()) ? "X" 
						: "S"; // deal with others as they arise
		}

		// added now that bigint has shown.
		// Examining the code appears to show that this test is mainly to 
		// deterine whether or not to put quotes around a search clause.
		// I'm adding type N to the true test, but this makes the method name confusing,
		public static bool IsIntegerType(String type)
		{
			return (type == "I" || type == "J" || type == "N") ? true : false;
		}

		public String ToXml()
		{
			String ordAtt = (this.order == "D") ? " Ord=\"D\"" : "";
			return String.Format("<FIELD Name=\"{0}\" Type=\"{1}\"{2}/>", this.name, this.type, ordAtt);
		}

		public SchemaField(XmlNode fieldNode)
		{
			this.name = fieldNode.Attributes["Name"].Value;
			this.type = fieldNode.Attributes["Type"].Value;
			XmlAttribute att = fieldNode.Attributes["Ord"];
			if (att != null) this.order = att.Value;
		}

		String name;
		String type;
		String order = "";
	}

	/*
	<VIEWDEF Name="xxx" TableName="yyy"> 
	<SCHEMA>
	<FIELD Name="Abc" Type="S|I|N|D"/>
	</SCHEMA>
	<VIEWDEF>
	<SHOW>
	<FIELD Name="Abc" Type="S|I|N|D"/>
	<SHOW/>
	<SORT>
	<FIELD Name="Abc" Type="S|I|N|D" Ord="A|D"/>
	</SORT>
	<SEARCH>
	<FIELD Name="Abc" Type="S|I|N|D"/>
	</SEARCH>
	<OWNERS>
	<OWNER Name="connection"/>
	</OWNERS>
	</VIEWDEF>
	
	A view definition is based on an Xml node. The document and parent node are
	supplied by the caller
	*/
	public class ViewDefinition
	{
		// Multiple constructors for different purposes.

		// Xml constructor. 
		// The node may be empty or a current view definition ie. read from 
		// a config. file.
		public ViewDefinition(XmlDocument doc, XmlNode node)
		{
			this.document = doc;
			this.viewDef = node;
			// add a name attribute if necessary
			if (this.viewDef.Attributes["Name"] == null)
			{
				this.AddAttribute(this.viewDef, "Name", "");
				this.AddAttribute(this.viewDef, "TableName", "");
				this.AddAttribute(this.viewDef, "Temp", "Y"); // new nodes are temporary views
			}
		}

		public ViewDefinition()
		{
			this.document = new XmlDocument();
			this.viewDef = this.document.CreateElement("DOC");
			this.document.AppendChild(this.viewDef);
			// add a name attribute if necessary
			this.AddAttribute(this.viewDef, "Name", "");
			this.AddAttribute(this.viewDef, "TableName", "");
			this.AddAttribute(this.viewDef, "Temp", "Y"); // new nodes are temporary views
		}

		public override string ToString()
		{
			return this.viewDef.OuterXml;
		}

		// infers the view definition from a datatable 
		public void ConfigureFromTable(DataTable dt)
		{
			this.Name = dt.TableName;
			this.TableName = dt.TableName;
			int colCount = 0;
			foreach (DataColumn col in dt.Columns)
			{
				String type = SchemaField.TranslateSystemType(col.DataType.ToString());
				SchemaField field = new SchemaField(col.ColumnName, type);
				this.AddField(field);
				this.AddToDisplay(field);
				if (colCount < 3)
					// below prevents inappropriate (eg. date) fields being added to
					// the filter.
					if (SchemaField.IsIntegerType(field.Type) || field.Type == "S")
						this.AddSearch(field);
				++colCount;
			}
		}

		// 
		public XmlNode Node { get { return this.viewDef; } }
		public String Name
		{
			get { return this.viewDef.Attributes["Name"].Value; }
			set { this.viewDef.Attributes["Name"].Value = value; }
		}
		public String TableName
		{
			get { return this.viewDef.Attributes["TableName"].Value; }
			set { this.viewDef.Attributes["TableName"].Value = value; }
		}

		// access collections of elements
		public SchemaField[] SchemaFields { get { return this.FieldsOf(this.Schema); } }
		public SchemaField[] DisplayFields { get { return this.FieldsOf(this.Display); } }
		public SchemaField[] SearchFields { get { return this.FieldsOf(this.Search); } }
		public SchemaField[] SortFields { get { return this.FieldsOf(this.Sort); } }
		// A view is permanent if the temp attribute is missing
		public bool IsPermanent { get { return (this.viewDef.Attributes["Temp"] == null); } }
		// A view is verified if the Unverified attribute is not present. 
		public bool IsVerified { get { return (this.viewDef.Attributes[DbView.Resources.ConfigDoc.CFGATT_UNTESTED] == null); } }
		
		public bool IsCustom
		{
			get
			{
				// A view is custom if the table def has ( + select + )
				return (this.TableName.Contains("(") && 
						this.TableName.Contains(")") && 
						this.TableName.ToUpper().Contains("SELECT"));
			}
		}

		public String FilterClause
		{
			get { return this.ExtractFilterClause(); }
			set { this.SetFilterClause(value); }
		}
		public String ExtraClause
		{
			get { return this.ExtractExtraClause(); }
			set { this.SetExtraClause(value); }
		}

		public void SetPermanent(bool permanent)
		{
			if (!permanent)
				// flag as temporary
				this.AddAttribute(this.viewDef, "Temp", "Y");
			else
			{
				XmlAttribute tempAtt = this.viewDef.Attributes["Temp"];
				if (tempAtt != null) this.viewDef.Attributes.Remove(tempAtt);
			}
		}

		//
		// Part of a scheme for identifying and eventually fixing broken views.
		//
		public void SetTested(bool isTested)
		{
			if (!isTested)
				// flag as temporary
				this.AddAttribute(this.viewDef, DbView.Resources.ConfigDoc.CFGATT_UNTESTED, "Y");
			else
			{
				XmlAttribute tempAtt = this.viewDef.Attributes[DbView.Resources.ConfigDoc.CFGATT_UNTESTED];
				if (tempAtt != null) this.viewDef.Attributes.Remove(tempAtt);
			}
		}

		// Api assumes you can attach and detach a view from many connections
		// the current implementation. Tag is a Connection name
		public void SetOwner(String tag, bool attach)
		{
			String xpath = String.Format("OWNER[@Name='{0}']", tag);
			XmlNode node = this.Owners.SelectSingleNode(xpath);
			// add if the node does not exist
			if (attach && node == null)
				this.CreateOwnerNode(tag);
			// remove if the node exists
			else if (!attach && node != null)
				this.Owners.RemoveChild(node);
		}

		// populate a elements
		public void AddField(SchemaField field)
		{
			XmlNode node = this.CreateFieldNode(field);
			this.Schema.AppendChild(node);
		}

		public void AddToDisplay(SchemaField field)
		{
			XmlNode node = this.CreateFieldNode(field);
			this.Display.AppendChild(node);
		}

		public void AddSort(SchemaField field, bool descending)
		{
			// TODO: take account of descending.
			XmlNode node = this.CreateFieldNode(field);
			this.Sort.AppendChild(node);
			if (descending)
				this.AddAttribute(node, "Ord", "D");
		}

		public void ReplaceSort(SchemaField field, bool descending)
		{
			// remove all other sort keys
			while (this.Sort.HasChildNodes)
				this.Sort.RemoveChild(this.Sort.FirstChild);
			// replace with new one (Hack! null clears the sort)
			if (field != null)
				this.AddSort(field, descending);
		}

		public void AddSearch(SchemaField field)
		{
			XmlNode node = this.CreateFieldNode(field);
			this.Search.AppendChild(node);
		}

		String ExtractFilterClause()
		{
			XmlNode node = this.GetFilterClauseNode(false);
			if (node != null) return node.InnerText;
			return "";
		}

		private void SetFilterClause(String clause)
		{
			XmlNode node = this.GetFilterClauseNode(true);
			node.InnerText = clause;
		}

		private XmlNode GetFilterClauseNode(bool createWhenMissing)
		{
			String filterElementName = "FILTER";
			String xpath = String.Format("{0}", filterElementName);
			XmlNode node = this.viewDef.SelectSingleNode(xpath);
			if (node == null && createWhenMissing)
			{
				node = this.document.CreateElement(filterElementName);
				this.viewDef.AppendChild(node);
			}
			return node;
		}

		String ExtractExtraClause()
		{
			XmlNode node = this.GetExtraClauseNode(false);
			if (node != null) return node.InnerText;
			return "";
		}

		private void SetExtraClause(String clause)
		{
			XmlNode node = this.GetExtraClauseNode(true);
			node.InnerText = clause;
		}

		private XmlNode GetExtraClauseNode(bool createWhenMissing)
		{
			String extraClauseElementName = "EXTRA";
			String xpath = String.Format("{0}", extraClauseElementName);
			XmlNode node = this.viewDef.SelectSingleNode(xpath);
			if (node == null && createWhenMissing)
			{
				node = this.document.CreateElement(extraClauseElementName);
				this.viewDef.AppendChild(node);
			}
			return node;
		}

		// how to update from the edit view dialog
		public void Copy(ViewDefinition source)
		{
			this.viewDef.InnerXml = source.Node.InnerXml;
			// fix up the view attibutes
			this.SetPermanent(source.IsPermanent);
			this.Name = source.Name;
			// below should be copied, but isn't due to the complex view thingy.
			// this.TableName = source.TableName;
			// add others where necessary
		}

		//// Looking ahead
		//public ViewDefinition MakeClone()
		//{
		//    ViewDefinition theClone = new ViewDefinition();
		//    theClone.Node.InnerXml = this.Node.InnerXml;
		//    // fix up the view attibutes
		//    theClone.SetPermanent(this.IsPermanent);
		//    theClone.Name = this.Name;
		//    // add others where necessary
		//    return theClone;
		//}

		// for a view that fails to execute try to fix it so that it will
		public bool AttemptFix()
		{
			bool modified = false;
			// step 1 remove filter clause that may be generating invalid SQL
			if (this.ExtraClause.Length > 0)
			{
				this.SetExtraClause("");
				modified = true;
			}
			// step 2 remove sort keys which may be causing a timeout.
			if (this.Sort.ChildNodes.Count > 0)
			{
				this.Sort.RemoveAll();
				modified = true;
			}
			return modified;
		}

		// for a view that fails to execute as it contains an invalid field 
		// eg. one removed from the table.
		public bool AttemptFix(DbView.FieldInfo [] schema)
		{
			bool modified = false; // all views are checked. Almost all will be fine and therefore not modified
			// Check each set of field lists in turn: the schema, seach list, sort list and filter search lists.
			// Check all the fields listed against the schema obtained fresh from the database and remove any not found
			// set the global return if any list was modified.
			if (this.CheckFixFieldNode(this.Schema, schema)) modified = true;
			if (this.CheckFixFieldNode(this.Display, schema)) modified = true;
			if (this.CheckFixFieldNode(this.Search, schema)) modified = true;
			if (this.CheckFixFieldNode(this.Sort, schema)) modified = true;
			return modified;
		}
		// Remove duff fields from a single XmlNode
		private bool CheckFixFieldNode(XmlNode fieldNode, FieldInfo[] schema)
		{
			// collect the duff fields.
			List<String> killFields = new List<string>();
			// obtain the fields from the node.
			SchemaField[] list = this.FieldsOf(fieldNode);
			// match each field from the node to the schema. If there is not match 
			// add the name to the list to be removed.
			foreach (SchemaField f in list)
			{
				bool found = false;
				foreach (FieldInfo fi in schema)
				{
					if (f.Name == fi.Name)
					{
						found = true;
						break;
					}
				}
				if (found == false)
					killFields.Add(f.Name);
			}
			// Normal situation is there is nothing to do so break off here.
			if (killFields.Count == 0)
				return false;
			
			// something to do (rare).
			foreach (String name in killFields)
			{
				// find the field node
				String xpath = String.Format("FIELD[@Name='{0}']", name);
				XmlNode n = fieldNode.SelectSingleNode(xpath);
				// remove it.
				if (n != null)
					n.ParentNode.RemoveChild(n);
				// this check will be perform 3 times unnecessarily, but here is a 
				// convenient place and the inefficiency is not an issue
				// So clear the filter clause if the removed field is in it.
				if (this.ExtraClause.IndexOf(name) != -1)
					this.SetExtraClause("");
			}
			return true;  // ie. the definition was modified
		}

		/////////////////////////// private stuff from here /////////////////////////
		XmlDocument document; // just held for creating elements and attributes.
		XmlNode viewDef;		// the node that holds the view definition

		XmlNode Schema { get { return this.GetSection("SCHEMA"); } }
		XmlNode Sort { get { return this.GetSection("SORT"); } }
		XmlNode Search { get { return this.GetSection("SEARCH"); } }
		XmlNode Display { get { return this.GetSection("SHOW"); } }
		XmlNode Owners { get { return this.GetSection("OWNERS"); } }

		// return the schema info for a field given its name.
		public SchemaField this[String s]
		{
			get
			{
				String xpath = String.Format("FIELD[@Name=\"{0}\"]", s);
				XmlNode node = this.Schema.SelectSingleNode(xpath);
				SchemaField field = new SchemaField(node);
				return field;
			}
		}

		// returns a node for each family of fields, adding the node if it is
		// not there.
		private XmlNode GetSection(String tag)
		{
			String xpath = String.Format("{0}", tag);
			XmlNode node = this.viewDef.SelectSingleNode(xpath);
			if (node != null) return node;
			node = this.document.CreateElement(tag);
			this.viewDef.AppendChild(node);
			return node;
		}

		// Returns an array of different type fields eg. report, sort, search etc.
		private SchemaField[] FieldsOf(XmlNode node)
		{
			XmlNodeList fieldNodes = node.SelectNodes("FIELD");
			int count = fieldNodes.Count;
			SchemaField[] fields = new SchemaField[count];
			int idx = 0;
			foreach (XmlNode fieldNode in fieldNodes)
			{
				fields[idx++] = new SchemaField(fieldNode);
			}
			return fields;
		}

		// At present each report/search/sort/filter element is a whole field
		// copied from the schema.
		private XmlNode CreateFieldNode(SchemaField field)
		{
			XmlNode node = this.document.CreateElement("FIELD");
			XmlAttribute att = this.document.CreateAttribute("Name");
			att.Value = field.Name;
			node.Attributes.Append(att);
			att = this.document.CreateAttribute("Type");
			att.Value = field.Type;
			node.Attributes.Append(att);
			return node;
		}

		private void CreateOwnerNode(String tag)
		{
			XmlNode node = this.document.CreateElement("OWNER");
			XmlAttribute att = this.document.CreateAttribute("Name");
			att.Value = tag;
			node.Attributes.Append(att);
			this.Owners.AppendChild(node);
		}

		// generic: ought to mode to XmlHelper class.
		private void AddAttribute(XmlNode node, String attName, String attValue)
		{
			XmlAttribute att = this.document.CreateAttribute(attName);
			att.Value = attValue;
			node.Attributes.Append(att);
		}
	}
}
